package devicedbgui.data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;

/** Interface to database for acquiring Test Result information */
public class TestResultDAO {
     /** Stores Connection status */
    private Connection conn;
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    /** Constructor used to pass connection status to object
     * @param conn  connection status
     */
    public TestResultDAO(Connection conn)
    {
        this.conn = conn;
    }
    /** Selects all test results in database
     * 
     * @return result
     * @throws java.lang.Exception
     */
    public ArrayList<TestResult> selectAll() throws Exception {
        ArrayList<TestResult> result = new ArrayList<TestResult>();

        try {
            String query = "SELECT * FROM TestResult;";           
            PreparedStatement pstmt = conn.prepareStatement(query);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                TestResult pObj = new TestResult(
                        rs.getInt("idTestResult"),   
                        rs.getString("name"),
                        rs.getString("description"),
                        rs.getTimestamp("time"),
                        rs.getInt("TestCase_idTestCase")
                        );
                result.add(pObj);
            }

        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }
        return result;
    }
    /** Searches database by particular id
     * 
     * @param id 
     * @return result
     * @throws java.lang.Exception
     */
    public ArrayList<TestResult> searchByID(int id) throws Exception {
        ArrayList<TestResult> result = new ArrayList<TestResult>();

        try {
            String query = "SELECT tr.*, tc.description as testCaseDescription, " +
                    "e.description as exeDescription, d.name as deviceName, d.serial as deviceSerial " + 
                    "FROM TestResult tr, TestCase tc, exe e, Build b, Device d " +
                    "where tr.TestCase_idTestCase = tc.idTestCase and " + 
                    "tr.TestCase_idTestCase = tc.idTestCase and " + 
                    "tc.exe_idexe = e.idexe and " + 
                    "e.Build_idBuild = b.idBuild and " + 
                    "b.Device_record = d.record and " + 
                    "tr.idTestResult = " + id + 
                    ";";           
            PreparedStatement pstmt = conn.prepareStatement(query);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                TestResult pObj = new TestResult(
                        rs.getInt("idTestResult"),   
                        rs.getString("name"),
                        rs.getString("description"),
                        rs.getTimestamp("time"),
                        rs.getInt("TestCase_idTestCase")
                        );
                pObj.testCaseDescription = rs.getString("testCaseDescription");
                pObj.exeDescription = rs.getString("exeDescription");
                pObj.deviceName = rs.getString("deviceName");
                pObj.deviceSerial = rs.getString("deviceSerial");
                result.add(pObj);
            }
        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }
        return result;
    }
    /** Searches database by particular name
     * 
     * @param name 
     * @return result
     * @throws java.lang.Exception
     */
    public ArrayList<TestResult> searchByName(String name) throws Exception {
        ArrayList<TestResult> result = new ArrayList<TestResult>();

        try {            
            String query = "SELECT tr.*, tc.description as testCaseDescription, " +
                    "e.description as exeDescription, d.name as deviceName, d.serial as deviceSerial " + 
                    "FROM TestResult tr, TestCase tc, exe e, Build b, Device d " +
                    "where tr.TestCase_idTestCase = tc.idTestCase and " + 
                    "tr.TestCase_idTestCase = tc.idTestCase and " + 
                    "tc.exe_idexe = e.idexe and " + 
                    "e.Build_idBuild = b.idBuild and " + 
                    "b.Device_record = d.record and " + 
                    "tr.name like '%" + name + 
                    "%';";           
            PreparedStatement pstmt = conn.prepareStatement(query);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                TestResult pObj = new TestResult(
                        rs.getInt("idTestResult"),   
                        rs.getString("name"),
                        rs.getString("description"),
                        rs.getTimestamp("time"),
                        rs.getInt("TestCase_idTestCase")
                        );
                pObj.testCaseDescription = rs.getString("testCaseDescription");
                pObj.exeDescription = rs.getString("exeDescription");
                pObj.deviceName = rs.getString("deviceName");
                pObj.deviceSerial = rs.getString("deviceSerial");
                result.add(pObj);
            }

        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }
        return result;
    }
    /** Searches database by test result description
     * 
     * @param description 
     * @return result
     * @throws java.lang.Exception
     */
    public ArrayList<TestResult> searchByDescription(String description) throws Exception {
        ArrayList<TestResult> result = new ArrayList<TestResult>();

        try {
            String query = "SELECT tr.*, tc.description as testCaseDescription, " +
                    "e.description as exeDescription, d.name as deviceName, d.serial as deviceSerial " + 
                    "FROM TestResult tr, TestCase tc, exe e, Build b, Device d " +
                    "where tr.TestCase_idTestCase = tc.idTestCase and " + 
                    "tr.TestCase_idTestCase = tc.idTestCase and " + 
                    "tc.exe_idexe = e.idexe and " + 
                    "e.Build_idBuild = b.idBuild and " + 
                    "b.Device_record = d.record and " + 
                    "tr.description like '%" + description + 
                    "%';";           
            PreparedStatement pstmt = conn.prepareStatement(query);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                TestResult pObj = new TestResult(
                        rs.getInt("idTestResult"),   
                        rs.getString("name"),
                        rs.getString("description"),
                        rs.getTimestamp("time"),
                        rs.getInt("TestCase_idTestCase")
                        );
                pObj.testCaseDescription = rs.getString("testCaseDescription");
                pObj.exeDescription = rs.getString("exeDescription");
                pObj.deviceName = rs.getString("deviceName");
                pObj.deviceSerial = rs.getString("deviceSerial");
                result.add(pObj);
            }

        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }
        return result;
    }
    
//    public ArrayList<TestResult> searchByTime(Date time) throws Exception {
//        ArrayList<TestResult> result = new ArrayList<TestResult>();
//
//        try {
//            String query = "SELECT * FROM TestResult where description like '%" + description +"%';";
//            PreparedStatement pstmt = conn.prepareStatement(query);
//            ResultSet rs = pstmt.executeQuery();
//            while (rs.next()) {
//                TestResult pObj = new TestResult(
//                        rs.getInt("idTestResult"),   
//                        rs.getString("name"),
//                        rs.getString("description"),
//                        rs.getTimestamp("time"),
//                        rs.getInt("TestCase_idTestCase")
//                        );
//                result.add(pObj);
//            }
//
//        } catch (Exception e) {
//            throw new Exception(e.getMessage());
//        }
//        return result;
//    }
    /** Searches database by test case id
     * 
     * @param idTestCase 
     * @return result
     * @throws java.lang.Exception
     */
    public ArrayList<TestResult> searchByTestCaseID(int idTestCase) throws Exception {
        ArrayList<TestResult> result = new ArrayList<TestResult>();

        try {
            String query = "SELECT tr.*, tc.description as testCaseDescription, " +
                    "e.description as exeDescription, d.name as deviceName, d.serial as deviceSerial " + 
                    "FROM TestResult tr, TestCase tc, exe e, Build b, Device d " +
                    "where tr.TestCase_idTestCase = tc.idTestCase and " + 
                    "tr.TestCase_idTestCase = tc.idTestCase and " + 
                    "tc.exe_idexe = e.idexe and " + 
                    "e.Build_idBuild = b.idBuild and " + 
                    "b.Device_record = d.record and " + 
                    "tr.TestCase_idTestCase = " + idTestCase + 
                    ";";           
            PreparedStatement pstmt = conn.prepareStatement(query);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                TestResult pObj = new TestResult(
                        rs.getInt("idTestResult"),   
                        rs.getString("name"),
                        rs.getString("description"),
                        rs.getTimestamp("time"),
                        rs.getInt("TestCase_idTestCase")
                        );
                pObj.testCaseDescription = rs.getString("testCaseDescription");
                pObj.exeDescription = rs.getString("exeDescription");
                pObj.deviceName = rs.getString("deviceName");
                pObj.deviceSerial = rs.getString("deviceSerial");
                result.add(pObj);
            }

        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }
        return result;
    }
    /** Select all test results
     * @return result
     * @throws Exception 
     */
    public ArrayList<TestResult> selectAllEx() throws Exception {
        ArrayList<TestResult> result = new ArrayList<TestResult>();

        try {
            String query = "SELECT tr.*, tc.description as testCaseDescription, " +
                    "e.description as exeDescription, d.name as deviceName, d.serial as deviceSerial " + 
                    "FROM TestResult tr, TestCase tc, exe e, Build b, Device d " +
                    "where tr.TestCase_idTestCase = tc.idTestCase and " + 
                    "tr.TestCase_idTestCase = tc.idTestCase and " + 
                    "tc.exe_idexe = e.idexe and " + 
                    "e.Build_idBuild = b.idBuild and " + 
                    "b.Device_record = d.record" + 
                    " order by idTestResult;";           
            PreparedStatement pstmt = conn.prepareStatement(query);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                TestResult pObj = new TestResult(
                        rs.getInt("idTestResult"),   
                        rs.getString("name"),
                        rs.getString("description"),
                        rs.getTimestamp("time"),
                        rs.getInt("TestCase_idTestCase")
                        );
                pObj.testCaseDescription = rs.getString("testCaseDescription");
                pObj.exeDescription = rs.getString("exeDescription");
                pObj.deviceName = rs.getString("deviceName");
                pObj.deviceSerial = rs.getString("deviceSerial");
                result.add(pObj);
            }

        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }
        return result;
    }
     /** Inserts a new test result into database
     * 
     * @param pObj
     * @throws java.lang.Exception
     */
    public void insert(TestResult pObj) throws Exception {
       
        try {
            String insert = "INSERT INTO TestResult(" +
                    "name," +
                    "description," +
                    "time," +                    
                    "TestCase_idTestCase)" +
                    "VALUES('" + pObj.name + "','" +
                                 pObj.description + "','" +                                
                                 sdf.format(pObj.time) + "','" +
                                 pObj.TestCase_idTestCase + "');";

            
            PreparedStatement pstmt = conn.prepareStatement(insert);
            pstmt.executeUpdate();            

        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }
    }
/** Updates database with the modified test result information 
 * 
 * @param pObj
 * @throws java.lang.Exception
 */
    public void update(TestResult pObj) throws Exception {        
        try {

            String update = "UPDATE TestResult SET " +
                    "name = '" + pObj.name + "'," +
                    "description = '" + pObj.description +  "'," +
                    "time = '" + sdf.format(pObj.time) +  "'," +
                    "TestCase_idTestCase = '" + pObj.TestCase_idTestCase +
                    "' WHERE idTestResult = '" + pObj.idTestResult + "';";
            
           
            PreparedStatement pstmt = conn.prepareStatement(update);
            pstmt.executeUpdate();
            pstmt.close();
           

        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }
    }
/** Deletes a value from database
 * 
 * @param pId
 * @throws java.lang.Exception
 */
    public void delete(int pId) throws Exception {
       
        try {
            String delete = "DELETE FROM TestResult WHERE idTestResult = '" + pId + "';";
          
            PreparedStatement pstmt = conn.prepareStatement(delete);
            pstmt.executeUpdate();
           

        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }
    }
    /** Chooses test result based on device record
     * 
     * @param deviceRecord 
     * @return result
     * @throws java.lang.Exception
     */
    public ArrayList<TestResult> selectByDevice(String deviceRecord) throws Exception {
        ArrayList<TestResult> result = new ArrayList<TestResult>();
        try {
            String query = "SELECT tr.* FROM Build b, exe e, TestCase tc, TestResult tr " + 
                    " where b.Device_record = '" + deviceRecord +
                    "' and b.idBuild = e.Build_idBuild and e.idexe = tc.exe_idexe and tc.idTestCase = tr.TestCase_idTestCase;";           
            PreparedStatement pstmt = conn.prepareStatement(query);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                TestResult pObj = new TestResult(
                        rs.getInt("idTestResult"),   
                        rs.getString("name"),
                        rs.getString("description"),
                        rs.getTimestamp("time"),
                        rs.getInt("TestCase_idTestCase")
                        );
                result.add(pObj);
            }

        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }
        return result;
    } 
    /** Searches database by particular test case id
     * 
     * @param idTestCase 
     * @return result
     * @throws java.lang.Exception
     */
    public ArrayList<TestResult> selectByTestCase(int idTestCase) throws Exception {
        ArrayList<TestResult> result = new ArrayList<TestResult>();
        try {
            String query = "SELECT * FROM TestResult where TestCase_idTestCase = " + idTestCase +";";           
            PreparedStatement pstmt = conn.prepareStatement(query);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                TestResult pObj = new TestResult(
                        rs.getInt("idTestResult"),   
                        rs.getString("name"),
                        rs.getString("description"),
                        rs.getTimestamp("time"),
                        rs.getInt("TestCase_idTestCase")
                        );
                result.add(pObj);
            }

        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }
        return result;
    }
    /** Searches database by particular device record
     * 
     * @param deviceRecord 
     * @return result
     * @throws java.lang.Exception
     */
    public ArrayList<TestResult> selectByDeviceEx(String deviceRecord) throws Exception {
        ArrayList<TestResult> result = new ArrayList<TestResult>();
        try {
            String query = "SELECT tr.*, tc.description as testCaseDescription, " +
                    "e.description as exeDescription, d.name as deviceName, d.serial as deviceSerial " + 
                    "FROM TestResult tr, TestCase tc, exe e, Build b, Device d " +
                    "where tr.TestCase_idTestCase = tc.idTestCase and " + 
                    "tr.TestCase_idTestCase = tc.idTestCase and " + 
                    "tc.exe_idexe = e.idexe and " + 
                    "e.Build_idBuild = b.idBuild and " + 
                    "b.Device_record = d.record and " + 
                    "d.record = '" + deviceRecord + 
                    "' order by idTestResult;";           
            PreparedStatement pstmt = conn.prepareStatement(query);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                TestResult pObj = new TestResult(
                        rs.getInt("idTestResult"),   
                        rs.getString("name"),
                        rs.getString("description"),
                        rs.getTimestamp("time"),
                        rs.getInt("TestCase_idTestCase")
                        );
                pObj.testCaseDescription = rs.getString("testCaseDescription");
                pObj.exeDescription = rs.getString("exeDescription");
                pObj.deviceName = rs.getString("deviceName");
                pObj.deviceSerial = rs.getString("deviceSerial");
                result.add(pObj);
            }

        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }
        return result;
    } 
    /** Searches database by particular test case id
     * 
     * @param idTestCase 
     * @return result
     * @throws java.lang.Exception
     */
    public ArrayList<TestResult> selectByTestCaseEx(int idTestCase) throws Exception {
        ArrayList<TestResult> result = new ArrayList<TestResult>();
        try {
            String query = "SELECT tr.*, tc.description as testCaseDescription, " +
                    "e.description as exeDescription, d.name as deviceName, d.serial as deviceSerial " + 
                    "FROM TestResult tr, TestCase tc, exe e, Build b, Device d " +
                    "where tr.TestCase_idTestCase = tc.idTestCase and " + 
                    "tr.TestCase_idTestCase = tc.idTestCase and " + 
                    "tc.exe_idexe = e.idexe and " + 
                    "e.Build_idBuild = b.idBuild and " + 
                    "b.Device_record = d.record and " + 
                    "tr.TestCase_idTestCase = " + idTestCase + 
                    " order by idTestResult;";           
            PreparedStatement pstmt = conn.prepareStatement(query);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                TestResult pObj = new TestResult(
                        rs.getInt("idTestResult"),   
                        rs.getString("name"),
                        rs.getString("description"),
                        rs.getTimestamp("time"),
                        rs.getInt("TestCase_idTestCase")
                        );
                pObj.testCaseDescription = rs.getString("testCaseDescription");
                pObj.exeDescription = rs.getString("exeDescription");
                pObj.deviceName = rs.getString("deviceName");
                pObj.deviceSerial = rs.getString("deviceSerial");
                result.add(pObj);
            }

        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }
        return result;
    }
    
    /** Get Test Case Details
     * @param testCase
     * @return
     * @throws Exception 
     */
    public ArrayList<TestCaseDetails> getTestCaseDetails(String testCase) throws Exception
    {
        ArrayList<TestCaseDetails> ret = new ArrayList<TestCaseDetails>();
        try {
            // get failed
            String query = "select tr.name as statement, count(*) as numOfFailed from TestResult tr, TestCase tc" +
                    " where tr.TestCase_idTestCase = tc.idTestCase and tc.description like '" + testCase + ";%' and tr.description = 'fail' group by tr.name;";
            
            PreparedStatement pstmt = conn.prepareStatement(query);
            ResultSet rs = pstmt.executeQuery();           
            while (rs.next()) {
                TestCaseDetails pObj = new TestCaseDetails();
                pObj.statement = rs.getString("statement");
                pObj.numberOfFailed = rs.getInt("numOfFailed");
                ret.add(pObj);
            }

            // continue to get passed
            ArrayList<TestCaseDetails> passed = new ArrayList<TestCaseDetails>();
            query = "select tr.name as statement, count(*) as numOfPassed from TestResult tr, TestCase tc" +
                    " where tr.TestCase_idTestCase = tc.idTestCase and tc.description like '" + testCase + ";%' and tr.description = 'pass' group by tr.name;";
            pstmt = conn.prepareStatement(query);
            rs = pstmt.executeQuery();           
            while (rs.next()) {
                TestCaseDetails pObj = new TestCaseDetails();
                pObj.statement = rs.getString("statement");
                pObj.numberOfPassed = rs.getInt("numOfPassed");
                passed.add(pObj);
            }
            
            // merge two arrays
            ArrayList<TestCaseDetails> toAdd = new ArrayList<TestCaseDetails>();
            for (TestCaseDetails pass : passed){
                    if (!ret.contains(pass))
                        toAdd.add(pass);
            }
            for (TestCaseDetails item : ret)
                for (TestCaseDetails pass : passed){
                    if (item.statement.equals(pass.statement))
                        item.numberOfPassed = pass.numberOfPassed;
                }
            ret.addAll(toAdd);
        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }
        return ret;
    }
}
